package edu.lsnu.equipment_management_system.mapper.select.paging;

import edu.lsnu.equipment_management_system.pojo.vo.out.admin.AdminLogVo;
import edu.lsnu.equipment_management_system.pojo.vo.sql_conditions.PageBeginAndPageSizeVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author mzy
 * @Description 管理员审核之后的日志
 * @date 2018/11/25
 */
public interface AdminReviewLogMapperWithPaging {
    // ----------------购入审核日志----------------
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "YHB.XM 'userName',\n" +
            "cast('购入设备' as varchar(20)) 'operating', \n" +
            "JBXX_SB.JHRQ 'date'\n" +
            "from DM_SBLB\n" +
            "inner join JBXX_SB \n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "inner join \n" +
            "( -- 找到第一个购买的人\n" +
            "\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.SYRQ1\n" +
            "\tfrom (\n" +
            "\t\tSELECT JBXX_SBSY.SBBH, min(JBXX_SBSY.SYRQ1) 'SYRQ1'\n" +
            "\t\tfrom JBXX_SBSY\n" +
            "\t\tgroup by JBXX_SBSY.SBBH \n" +
            "\t) TMP \n" +
            "\tinner join JBXX_SBSY \n" +
            "\ton JBXX_SBSY.SBBH = TMP.SBBH and JBXX_SBSY.SYRQ1 = TMP.SYRQ1 \n" +
            ") JBXX_SBSY_TMP\n" +
            "on JBXX_SBSY_TMP.SBBH = JBXX_SB.SBBH\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "where JBXX_SB.SBLY = '购入' and JBXX_SB.SHRQ is not null " +
            "order by date desc offset #{pageBegin} rows fetch next #{pageSize} rows only\n")
    List<AdminLogVo> purchaseReviewLog(PageBeginAndPageSizeVo pageBeginAndPageSizeVo);

    @Select("select \n" +
            "count(JBXX_SB.SBBH)\n" +
            "from DM_SBLB\n" +
            "inner join JBXX_SB \n" +
            "on DM_SBLB.SBLBDM = JBXX_SB.SBLBDM\n" +
            "inner join \n" +
            "( -- 找到第一个购买的人\n" +
            "\tselect JBXX_SBSY.SBBH, JBXX_SBSY.ZGH, JBXX_SBSY.SYRQ1\n" +
            "\tfrom (\n" +
            "\t\tSELECT JBXX_SBSY.SBBH, min(JBXX_SBSY.SYRQ1) 'SYRQ1'\n" +
            "\t\tfrom JBXX_SBSY\n" +
            "\t\tgroup by JBXX_SBSY.SBBH \n" +
            "\t) TMP \n" +
            "\tinner join JBXX_SBSY \n" +
            "\ton JBXX_SBSY.SBBH = TMP.SBBH and JBXX_SBSY.SYRQ1 = TMP.SYRQ1 \n" +
            ") JBXX_SBSY_TMP\n" +
            "on JBXX_SBSY_TMP.SBBH = JBXX_SB.SBBH\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_SBSY_TMP.ZGH\n" +
            "where JBXX_SB.SBLY = '购入' and JBXX_SB.SHRQ is not null ")
    int purchaseReviewLogCount();

    // ----------------报损报废审核日志----------------
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "YHB.XM 'userName',\n" +
            "cast('报损报废' as varchar(20)) 'operating', \n" +
            "JBXX_BSBF.SFRQ 'date'\n" +
            "from JBXX_BSBF\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_BSBF.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_BSBF.ZGH\n" +
            "where JBXX_BSBF.SHRQ is not null \n" +
            "order by date desc offset #{pageBegin} rows fetch next #{pageSize} rows only")
    List<AdminLogVo> damageReviewLog(PageBeginAndPageSizeVo pageBeginAndPageSizeVo);

    @Select("select \n" +
            "count(JBXX_SB.SBBH)\n" +
            "from JBXX_BSBF\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_BSBF.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_BSBF.ZGH\n" +
            "where JBXX_BSBF.SHRQ is not null")
    int damageReviewLogCount();

    // ----------------维修维护审核日志----------------
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "YHB.XM 'userName',\n" +
            "cast('设备维修' as varchar(20)) 'operating', \n" +
            "JBXX_WHWX.XHRQ1 'date'\n" +
            "from JBXX_WHWX\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_WHWX.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_WHWX.ZGH\n" +
            "where JBXX_WHWX.SHRQ is not null \n" +
            "order by date desc offset #{pageBegin} rows fetch next #{pageSize} rows only")
    List<AdminLogVo> repairReviewLog(PageBeginAndPageSizeVo pageBeginAndPageSizeVo);

    @Select("select \n" +
            "count(JBXX_SB.SBBH)\n" +
            "from JBXX_WHWX\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_WHWX.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_WHWX.ZGH\n" +
            "where JBXX_WHWX.SHRQ is not null \n")
    int repairReviewLogCount();

    // ---------------- 调入审核日志----------------
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "YHB.XM 'userName',\n" +
            "cast('设备调入' as varchar(20)) 'operating', \n" +
            "JBXX_DRDC.CRRQ 'date'\n" +
            "from JBXX_DRDC\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_DRDC.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "where JBXX_DRDC.DCDR = '调入' and JBXX_DRDC.SHRQ is not null \n" +
            "order by date desc offset #{pageBegin} rows fetch next #{pageSize} rows only")
    List<AdminLogVo> transferInReviewLog(PageBeginAndPageSizeVo pageBeginAndPageSizeVo);

    @Select("select \n" +
            "count(JBXX_SB.SBBH)\n" +
            "from JBXX_DRDC\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_DRDC.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "where JBXX_DRDC.DCDR = '调入' and JBXX_DRDC.SHRQ is not null ")
    int transferInReviewLogCount();

    // ----------------调出审核日志----------------
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "YHB.XM 'userName',\n" +
            "cast('设备调出' as varchar(20)) 'operating', \n" +
            "JBXX_DRDC.CRRQ 'date'\n" +
            "from JBXX_DRDC\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_DRDC.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "where JBXX_DRDC.DCDR = '调出' and JBXX_DRDC.SHRQ is not null \n" +
            "order by date desc offset #{pageBegin} rows fetch next #{pageSize} rows only")
    List<AdminLogVo> transferOutReviewLog(PageBeginAndPageSizeVo pageBeginAndPageSizeVo);

    @Select("select \n" +
            "count(JBXX_SB.SBBH)\n" +
            "from JBXX_DRDC\n" +
            "inner join JBXX_SB \n" +
            "on JBXX_SB.SBBH = JBXX_DRDC.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_DRDC.ZGH\n" +
            "where JBXX_DRDC.DCDR = '调出' and JBXX_DRDC.SHRQ is not null ")
    int transferOutReviewLogCount();

    // ----------------运行使用审核日志----------------
    @Select("select \n" +
            "DM_SBLB.SBLB 'equipmentCategory',\n" +
            "JBXX_SB.SBMC 'equipmentName',\n" +
            "JBXX_SB.SBBH 'equipmentNo',\n" +
            "YHB.XM 'userName',\n" +
            "cast('设备使用' as varchar(20)) 'operating', \n" +
            "JBXX_SBSY.SYRQ1 'date'\n" +
            "from JBXX_SBSY \n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_SBSY.ZGH\n" +
            "inner join JBXX_SB\n" +
            "on JBXX_SB.SBBH = JBXX_SBSY.SBBH\n" +
            "inner join DM_SBLB\n" +
            "on JBXX_SB.SBLBDM = DM_SBLB.SBLBDM\n" +
            "where JBXX_SBSY.SHRQ is not null \n" +
            "order by date offset #{pageBegin} rows fetch next #{pageSize} rows only\n")
    List<AdminLogVo> useRecordReviewLog(PageBeginAndPageSizeVo pageBeginAndPageSizeVo);

    @Select("select count(JBXX_SBSY.SYRQ1)\n" +
            "from JBXX_SBSY \n" +
            "inner join DM_CFCS \n" +
            "on DM_CFCS.CFCSDM = JBXX_SBSY.CFCSDM\n" +
            "inner join YHB\n" +
            "on YHB.ZGH = JBXX_SBSY.ZGH\n" +
            "inner join JBXX_SB\n" +
            "on JBXX_SB.SBBH = JBXX_SBSY.SBBH\n" +
            "where JBXX_SBSY.SHRQ is not null \n")
    int useRecordReviewLogCount();
}
